﻿Imports SIGI.Common.BE
Imports System.Data.SqlClient
Imports System.Configuration

Public Class ProfilesDAC

#Region "<< -Search- >>"
    Public Shared Function Search() As List(Of ProfileBE)
        Dim wSqlConn As SqlConnection = Nothing
        Dim wSqlCmd As SqlCommand = Nothing
        Dim wReader As SqlDataReader = Nothing
        Dim wConnStr As String = String.Empty

        Dim wProfileBEList As List(Of ProfileBE) = Nothing
        Dim wProfileBE As ProfileBE = Nothing

        Try
            'Obtengo la cadena de conexión
            wConnStr = ConfigurationManager.ConnectionStrings("connString").ConnectionString
            'Creo la conexión a la BD
            wSqlConn = New SqlConnection(wConnStr)
            'Creo el comando
            wSqlCmd = New SqlCommand()
            wSqlCmd.Connection = wSqlConn
            wSqlCmd.CommandType = CommandType.StoredProcedure
            wSqlCmd.CommandText = "Perfiles_s"

            'Abro la conexión a la BD
            wSqlConn.Open()

            wReader = wSqlCmd.ExecuteReader()
            'Leo los resultados de la consulta
            wProfileBEList = New List(Of ProfileBE)
            While wReader.Read()
                wProfileBE = New ProfileBE
                wProfileBE.Id = Convert.ToInt32(wReader("Id_Perfil"))
                wProfileBE.Description = Convert.ToString(wReader("Descripcion"))

                wProfileBEList.Add(wProfileBE)
            End While
            'Cierro la conexión a la BD
            wSqlConn.Close()
        Catch ex As Exception
            Throw ex
        Finally
            wReader = Nothing
            wSqlCmd = Nothing
            wSqlConn = Nothing
        End Try

        Return wProfileBEList
    End Function
#End Region

#Region "<< -Insert- >>"
    Public Shared Function Insert(pProfileDescription As String, pProfileScreenList As List(Of Profile_ScreenBE)) As Integer
        Dim wSqlConn As SqlConnection = Nothing
        Dim wSqlCmd As SqlCommand = Nothing
        Dim wSqlTran As SqlTransaction = Nothing
        Dim wReader As SqlDataReader = Nothing
        Dim wConnStr As String = String.Empty
        Dim wIdProfile As Integer = 0
        Dim wStrBuilder As System.Text.StringBuilder = Nothing

        Try
            'La lista jamas debe venir vacía
            If pProfileScreenList Is Nothing Or pProfileScreenList.Count = 0 Then
                Throw New Exception("Lista de Permisos por Pantallas vacía")
            End If

            'Obtengo la cadena de conexión
            wConnStr = ConfigurationManager.ConnectionStrings("connString").ConnectionString
            'Creo la conexión a la BD
            wSqlConn = New SqlConnection(wConnStr)

            wSqlConn.Open()

            'Creo la transacción
            wSqlTran = wSqlConn.BeginTransaction("Transaction")

            'Creo el comando
            wSqlCmd = New SqlCommand()
            wSqlCmd.Transaction = wSqlTran
            wSqlCmd.Connection = wSqlConn

            'Inserto en la tabla Perfiles
            wSqlCmd.CommandType = CommandType.StoredProcedure
            wSqlCmd.CommandText = "Perfiles_i"

            wSqlCmd.Parameters.Add("@Descripcion", SqlDbType.NVarChar, 50)
            wSqlCmd.Parameters("@Descripcion").Value = pProfileDescription

            wReader = wSqlCmd.ExecuteReader()
            If wReader.Read() Then
                wIdProfile = Convert.ToInt32(wReader("Id"))
            End If
            'Cierro el data reader
            wReader.Close()

            'Inserto en tabla Perfiles_Pantallas
            wStrBuilder = New System.Text.StringBuilder
            wSqlCmd.Parameters.Clear()
            wSqlCmd.CommandType = CommandType.Text

            For Each wProfileScreen In pProfileScreenList
                wStrBuilder.Append("EXEC PerfilesxPantalla_i ")
                wStrBuilder.Append(String.Format("@ID_Perfil = {0}, ", wIdProfile))
                wStrBuilder.Append(String.Format("@ID_Pantalla = {0}, ", wProfileScreen.IdScreen.Value))
                wStrBuilder.Append(String.Format("@ID_Permiso = {0}", wProfileScreen.IdPermission.Value))
                wStrBuilder.Append(vbNewLine)
            Next

            wSqlCmd.CommandText = wStrBuilder.ToString()
            wSqlCmd.ExecuteNonQuery()

            wSqlTran.Commit()
            wSqlConn.Close()
        Catch ex As Exception
            'Por si falla el rollback de la transacción
            Try
                wSqlTran.Rollback()
            Catch ex2 As Exception
                Throw ex2
            End Try

            Throw ex
        End Try

        Return wIdProfile
    End Function
#End Region

#Region "<< -Update- >>"
    Public Shared Sub Update(pPermissionxScreenList As List(Of Profile_ScreenBE))
        Dim wSqlConn As SqlConnection = Nothing
        Dim wSqlCmd As SqlCommand = Nothing
        Dim wSqlTran As SqlTransaction = Nothing
        Dim wReader As SqlDataReader = Nothing
        Dim wConnStr As String = String.Empty
        Dim wStrBuilder As System.Text.StringBuilder = Nothing

        Try
            'La lista jamas debe venir vacía
            If pPermissionxScreenList Is Nothing Or pPermissionxScreenList.Count = 0 Then
                Throw New Exception("Lista de Permisos por Pantallas vacía")
            End If

            'Obtengo la cadena de conexión
            wConnStr = ConfigurationManager.ConnectionStrings("connString").ConnectionString
            'Creo la conexión a la BD
            wSqlConn = New SqlConnection(wConnStr)

            wSqlConn.Open()

            'Creo la transacción
            wSqlTran = wSqlConn.BeginTransaction("Transaction")

            'Creo el comando
            wSqlCmd = New SqlCommand()
            wSqlCmd.Transaction = wSqlTran
            wSqlCmd.Connection = wSqlConn

            'Elimino los permisos asignados
            wSqlCmd.CommandType = CommandType.StoredProcedure
            wSqlCmd.CommandText = "PerfilesxPantalla_d_ByPerfilId"

            wSqlCmd.Parameters.Add("@ID_Perfil", SqlDbType.Int)
            wSqlCmd.Parameters("@ID_Perfil").Value = pPermissionxScreenList(0).IdProfile

            wSqlCmd.ExecuteNonQuery()

            'Inserto los nuevos permisos
            wStrBuilder = New System.Text.StringBuilder
            wSqlCmd.Parameters.Clear()
            wSqlCmd.CommandType = CommandType.Text

            For Each wProfileScreen In pPermissionxScreenList
                wStrBuilder.Append("EXEC PerfilesxPantalla_i ")
                wStrBuilder.Append(String.Format("@ID_Perfil = {0}, ", wProfileScreen.IdProfile.Value))
                wStrBuilder.Append(String.Format("@ID_Pantalla = {0}, ", wProfileScreen.IdScreen.Value))
                wStrBuilder.Append(String.Format("@ID_Permiso = {0}", wProfileScreen.IdPermission.Value))
                wStrBuilder.Append(vbNewLine)
            Next

            wSqlCmd.CommandText = wStrBuilder.ToString()
            wSqlCmd.ExecuteNonQuery()

            wSqlTran.Commit()
            wSqlConn.Close()

        Catch ex As Exception
            'Por si falla el rollback de la transacción
            Try
                wSqlTran.Rollback()
            Catch ex2 As Exception
                Throw ex2
            End Try

            Throw ex
        End Try
    End Sub
#End Region

#Region "<< -Delete- >>"
    Public Shared Sub Delete(pProfileId As Integer)
        Dim wSqlConn As SqlConnection = Nothing
        Dim wSqlCmd As SqlCommand = Nothing
        Dim wReader As SqlDataReader = Nothing
        Dim wConnStr As String = String.Empty

        Try
            'Obtengo la cadena de conexión
            wConnStr = ConfigurationManager.ConnectionStrings("connString").ConnectionString
            'Creo la conexión a la BD
            wSqlConn = New SqlConnection(wConnStr)
            wSqlCmd = New SqlCommand
            wSqlCmd.Connection = wSqlConn

            wSqlConn.Open()

            'Verifico si el perfil esta asociado a algun usuario
            wSqlCmd.CommandType = CommandType.Text
            wSqlCmd.CommandText = String.Format("SELECT COUNT(*) AS Count FROM Usuarios WHERE Id_Perfil = {0}", pProfileId)

            wReader = wSqlCmd.ExecuteReader()
            If wReader.Read() Then
                If Convert.ToInt32(wReader("Count")) > 0 Then
                    Throw New Exception("Existen Usuarios asignados al Perfil que desea eliminar")
                End If
                wReader.Close()

                'El perfil no esta asociado a ningún usuario. Procedo a eliminar el perfil.
                wSqlCmd.CommandType = CommandType.StoredProcedure
                wSqlCmd.CommandText = "Perfiles_d"

                wSqlCmd.Parameters.Add("@ID_Perfil", SqlDbType.Int)
                wSqlCmd.Parameters("@ID_Perfil").Value = pProfileId

                wSqlCmd.ExecuteNonQuery()
            Else
                Throw New Exception("No se pudo comprobar si el Perfil esta asignado a algún Usuario")
            End If

            wSqlConn.Close()
        Catch ex As Exception
            Throw ex
        End Try
    End Sub
#End Region

End Class
